﻿using Dapper;
using System;
using System.Text;
using System.Linq;
using SilenthinkContract;
using System.Collections.Generic;
using SilenthinkContract.Domain;
using SilenthinkCommon.Extension;

namespace SilenthinkService
{
    public class PassportService : ServiceBase, IPassportService
    {
        #region User
        
        public bool CreateUser(User o)
        {
            var result = false;
            var builderA = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Field("([Guid],[Origin],[Name],[Password],[NickName],[Role],[Email],[Mobile],[Level],[LastIpAddress],[LastLoginTime],[Status],[CreatedBy],[CreatedOn])VALUES(@Guid,@Origin,@Name,@Password,@NickName,@Role,@Email,@Mobile,@Level,@LastIpAddress,@LastLoginTime,@Status,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.Guid, o.Origin, o.Name, o.Password, o.NickName, o.Role, o.Email, o.Mobile, o.Level, o.LastIpAddress, o.LastLoginTime, o.Status, o.CreatedBy, o.CreatedOn });

            var templateA = builderA.AddTemplate(SqlTemplate.Insert);

            var profile = o.UserProfile;
            var builderB = new SqlBuilder().Table(Database.Passport.Table.UserProfile)
               .Field("([Id],[RealName],[Avatar],[Gender],[Birthday],[MaritalStatus],[Phone],[Mobile],[AreaCode],[Address])VALUES(@Id,@RealName,@Avatar,@Gender,@Birthday,@MaritalStatus,@Phone,@Mobile,@AreaCode,@Address)")
               .AddParameters(new { profile.Id, profile.RealName,profile.Avatar, profile.Gender, profile.Birthday,profile.MaritalStatus,profile.Phone,profile.Mobile,profile.AreaCode, profile.Address });
            var templateB = builderB.AddTemplate(SqlTemplate.Insert);

            var extra = o.UserExtra;
            var builderC = new SqlBuilder().Table(Database.Passport.Table.UserExtra)
                .Field("([Id])VALUES(@Id)")
                .AddParameters(new { extra.Id });

            var templateC = builderC.AddTemplate(SqlTemplate.Insert);

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    var userId = InsertReturnLastId(o, connection, templateA, transaction);
                    if (userId > 0)
                    {
                        o.Id = userId;
                        o.UserProfile.Id = userId;
                        o.UserExtra.Id = userId;

                        //处理UserProfile信息
                        var b = Execute(connection, templateB, transaction) == 1;

                        //处理UserExtra信息
                        var c = Execute(connection, templateC, transaction) == 1;

                        if (b&&c)
                        {
                            transaction.Commit();
                           
                        }
                        else
                        {
                            transaction.Rollback();
                        }
                    }
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    Log.Warn(e.Message);
                }
            }
            return result;
        }

        public bool UpdateUser(User o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Field("[NickName]=@NickName", new {o.NickName})
                .Field("[Role]=@Role", new {o.Role})
                .Field("[Email]=@Email", new {o.Email})
                .Field("[Mobile]=@Mobile", new {o.Mobile})
                .Field("[Level]=@Level", new {o.Level})
                .Field("[LastIpAddress]=@LastIpAddress", new {o.LastIpAddress})
                .Field("[LastLoginTime]=@LastLoginTime", new {o.LastLoginTime})
                .Field("[Status]=@Status", new {o.Status})
                .Where("[Id] = @Id", new {o.Id})
                .Where("[Version]=@Version", new {o.Version});
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteUser(long id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public User GetUser(long id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.User)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<User>(connection, template).FirstOrDefault();
            }
        }

        public PageList<User> GetPagedUser(string username = null, UserStatus? status = null, int index = 1,int size = 20)
        {
            var builder = new SqlBuilder().Field("*")
               .Table(Database.Passport.Table.User)
               .OrderBy("[CreatedOn] DESC")
               .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (!string.IsNullOrEmpty(username))
            {
                builder.Where("[Name] LIKE @Name", new { name = username + "%" });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return PageList<User>(connection, template);
            }
        }

        public User GetUser(long id, bool inclProfile = false, bool inclExtra = false)
        {
            User o;
            const string tpl = "SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;";
            var sb = new StringBuilder();
            sb.AppendFormat(tpl, Database.Passport.Table.User);
            if (inclProfile)
            {
                sb.AppendFormat(tpl, Database.Passport.Table.UserProfile);
            }
            if (inclExtra)
            {
                sb.AppendFormat(tpl, Database.Passport.Table.UserExtra);
            }

            var template = new SqlBuilder().AddTemplate(sb.ToString(), new {id});
            using (var connection = GetConnection(ConnectionName.Passport))
            using (var multi =connection.QueryMultiple(template))
            {
                o = multi.Read<User>().FirstOrDefault();
                if (o != null)
                {
                    if (inclProfile)
                    {
                        o.UserProfile = multi.Read<UserProfile>().FirstOrDefault();
                    }
                    if (inclExtra)
                    {
                        o.UserExtra = multi.Read<UserExtra>().FirstOrDefault();
                    }
                }
            }
            return o;
        }

        public User GetUser(string username)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.User)
                .Where("[Name]=@Name", new {name = username});

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<User>(connection, template).FirstOrDefault();
            }
        }

        public User GetUser(string name, string password)
        {
            var sb = new StringBuilder();

            sb.AppendLine(
                "if exists(select 0 from [User] a where a.[Name]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Name]=@Name and a.[Password]=@Password end");

            if (name.IsMobile())
            {
                sb.AppendLine(
                    "else if exists(select 0 from [User] a where a.[Mobile] is not null and a.[Mobile]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Mobile] is not null and a.[Mobile]=@Name and a.[Password]=@Password end");
            }
            else if (name.IsEmail())
            {
                sb.AppendLine(
                    "else if exists(select 0 from [User] a where a.[Email] is not null and a.[Email]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Email] is not null and a.[Email]=@Name and a.[Password]=@Password end");
            }

            sb.AppendLine("else begin select top 0 * from [User] where 1<>1 end");

            var template = new SqlBuilder().AddParameters(new {name, password}).AddTemplate(sb.ToString());
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<User>(connection, template).FirstOrDefault();
            }
        }

        public User GetUserByMobile(string mobile)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.User)
                .Where("[Mobile]=@Mobile", new {mobile});

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<User>(connection, template).FirstOrDefault();
            }
        }

        public bool ExistUserGuid(Guid guid)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Where("[Guid]=@Guid", new {guid});

            var template = builder.AddTemplate(SqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<int>(connection, template).First() > 0;
            }

        }

        public bool ExistUserName(string username)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Where("[Name]=@Name", new {name = username});

            var template = builder.AddTemplate(SqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<int>(connection, template).First() > 0;
            }
        }

        public bool ExistBindingEmail(string email)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Where("[Email]=@Email", new {email});

            var template = builder.AddTemplate(SqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<int>(connection, template).First() > 0;
            }
        }

        public bool ExistBindingMobile(string mobile)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Where("[Mobile]=@Mobile", new {mobile});

            var template = builder.AddTemplate(SqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<int>(connection, template).First() > 0;
            }
        }

        public bool EmailBinding(long userId, string email)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Field("[Email]=@Email", new {email})
                .Where("[Id]=@Id", new {Id = userId});
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) != 0;
            }
        }

        public bool MobileBinding(long userId, string mobile)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Field("[Mobile]=@Mobile", new {mobile})
                .Where("[Id]=@Id", new {Id = userId});

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) != 0;
            }
        }

        public bool ChangePassword(long userId, string password)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.User)
                .Field("[Password]=@Password", new {password})
                .Where("[Id]=@UserId", new {userId});

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) != 0;
            }
        }

        public UserProfile GetUserProfile(long id)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Passport.Table.UserProfile)
                .Where("[Id]=@Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserProfile>(connection, template).FirstOrDefault();
            }
        }

        public bool UpdateUserProfile(UserProfile o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserProfile)
                .Field("[RealName]=@RealName", new {o.RealName})
                .Field("[Birthday]=@Birthday", new {o.Birthday})
                .Field("[Gender]=@Gender", new {o.Gender})
                .Field("[MaritalStatus]=@MaritalStatus", new {o.MaritalStatus})
                .Field("[Phone]=@Phone", new {o.Phone})
                .Field("[Mobile]=@Mobile", new {o.Mobile})
                .Field("[Avatar]=@Avatar", new {o.Avatar})
                .Field("[AreaCode]=@AreaCode", new {o.AreaCode})
                .Field("[Address]=@Address", new {o.Address})
                .Where("[Id]=@Id", new {o.Id});

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public IList<OAuthConfig> GetOAuthConfigs(bool? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Passport.Table.OAuthConfig);
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new {status});
            }

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<OAuthConfig>(connection, template).ToList();
            }
        }

        #endregion

        #region UserAddress

        public UserAddress GetUserAddress(int id, long? userId = null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.UserAddress)
                .Where("[Id]=@Id", new {id});
            if (userId.HasValue)
            {
                builder.Where("[UserId]=@UserId", new {userId});
            }
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserAddress>(connection, template).FirstOrDefault();
            }
        }

        public IList<UserAddress> GetUserAddresses(long userId)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.UserAddress)
                .Where("[UserId]=@UserId", new {userId});

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserAddress>(connection,template).ToList();
            }
        }

        public bool CreateUserAddress(UserAddress o)
        {
            const string field =
                @"([UserId],[Category],[CountryId],[AreaCode],[Receiver],[Phone],[Mobile],[Address],[PostalCode],[IsDefault],[CreatedBy],[CreatedOn])
                                VALUES(@UserId,@Category,@CountryId,@AreaCode,@Receiver,@Phone,@Mobile,@Address,@PostalCode,@IsDefault,@CreatedBy,@CreatedOn)";
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserAddress)
                .Field(field)
                .AddParameters(
                    new
                        {
                            o.UserId,
                            o.Category,
                            o.CountryId,
                            o.AreaCode,
                            o.Receiver,
                            o.Phone,
                            o.Mobile,
                            o.Address,
                            o.PostalCode,
                            o.IsDefault,
                            o.CreatedBy,
                            o.CreatedOn
                        });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateUserAddress(UserAddress o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserAddress)
                .Field("[Category]=@Category", new {o.Category})
                .Field("[CountryId]=@CountryId", new {o.CountryId})
                .Field("[AreaCode]=@AreaCode", new {o.AreaCode})
                .Field("[Receiver]=@Receiver", new {o.Receiver})
                .Field("[Phone]=@Phone", new {o.Phone})
                .Field("[Mobile]=@Mobile", new {o.Mobile})
                .Field("[Address]=@Address", new {o.Address})
                .Field("[PostalCode]=@PostalCode", new {o.PostalCode})
                .Field("[IsDefault]=@IsDefault", new {o.IsDefault})
                .Where("[UserId]=@UserId AND [Id]=@Id", new {o.Id, o.UserId});

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteUserAddress(int id, long? userId = null)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserAddress)
                .Where("[Id]=@Id", new {id});
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new {userId});
            }

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) >= 1;
            }
        }

        public bool SetDefaultUserAddress(int id, long userId)
        {
            var builder = new SqlBuilder().AddParameters(new {id, userId});

            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set [IsDefault]=0 where [UserId]=@UserId AND [Id]<>@Id;",
                            Database.Passport.Table.UserAddress);
            sb.AppendFormat("update {0} set [IsDefault]=1 where [UserId]=@UserId AND [Id]=@Id;",
                            Database.Passport.Table.UserAddress);

            var template = builder.AddTemplate(sb.ToString());
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) >= 1;
            }
        }


        #endregion


        #region UserInvoice

        public UserInvoice GetUserInvoice(int id, long? userId = null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.UserInvoice)
                .Where("[Id]=@Id", new { id });
            if (userId.HasValue)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserInvoice>(connection, template).FirstOrDefault();
            }
        }

        public IList<UserInvoice> GetUserInvoices(long userId)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Passport.Table.UserInvoice)
                .Where("[UserId]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Query<UserInvoice>(connection, template).ToList();
            }
        }

        public bool CreateUserInvoice(UserInvoice o)
        {
            const string field =
                @"([UserId],[Category],[Title],[Content],[IsDefault],[CreatedBy],[CreatedOn])
                VALUES(@UserId,@Category,@Title,@Content,@IsDefault,@CreatedBy,@CreatedOn)";
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserInvoice)
                .Field(field)
                .AddParameters(new {o.UserId,o.Category,o.Title,o.Content,o.IsDefault,o.CreatedBy,o.CreatedOn});

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateUserInvoice(UserInvoice o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserInvoice)
                .Field("[Category]=@Category", new { o.Category })
                .Field("[Title]=@Title", new { o.Title })
                .Field("[Content]=@Content", new { o.Content })
                .Field("[IsDefault]=@IsDefault", new { o.IsDefault })
                .Where("[UserId]=@UserId AND [Id]=@Id", new { o.Id, o.UserId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteUserInvoice(int id, long? userId = null)
        {
            var builder = new SqlBuilder()
                .Table(Database.Passport.Table.UserInvoice)
                .Where("[Id]=@Id", new { id });
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool SetDefaultUserInvoice(int id, long userId)
        {
            var builder = new SqlBuilder().AddParameters(new { id, userId });

            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set [IsDefault]=0 where [UserId]=@UserId AND [Id]<>@Id;",
                            Database.Passport.Table.UserInvoice);
            sb.AppendFormat("update {0} set [IsDefault]=1 where [UserId]=@UserId AND [Id]=@Id;",
                            Database.Passport.Table.UserInvoice);

            var template = builder.AddTemplate(sb.ToString());
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return Execute(connection, template) == 1;
            }
        }


        #endregion
        #region Implementation of IService

        public int GetCachedSQLCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }

        public List<Tuple<string, string, int>> GetCachedSQL()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }

        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }

        #endregion

    }
}